<?php
/* MyMeeting is an online meeting management software.
 * Copyright (C) 2009 Government Of Malaysia
 * 
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License along
 * with this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 *
 * @author: Abdullah Zainul Abidin, Nuhaa All Bakry
 *          Teow Jit Huan, Tan Chin Voon
 *
 */
class Log extends AppModel {

    var $name = 'Log';
    var $validate = array(
        'user_id' => array('numeric'),
        'controller' => array('notempty'),
        'action' => array('notempty'),
        'url' => array('notempty')
    );

    //The Associations below have been created with all possible keys, those that are not needed can be removed
/*remove the user and log relation
    var $belongsTo = array(
            'User' => array('className' => 'User',
                                'foreignKey' => 'user_id',
                                'conditions' => '',
                                'fields' => '',
                                'order' => ''
            )
    );
*/    
    function getReport($type) {
        
        if ($type == 'USERS') {
            //get user and login information
            $sql3 = "select u.id, u.name, u.job_title, t.long_name
                    from titles as t, users as u
                    where u.deleted!='1' and (t.id=u.title_id or u.title_id=0)
                    group by u.id
                    order by u.name asc";
            $m = $this->query($sql3);
            $user_id=implode(',',Set::extract($m,'{n}.u.id'));
            
            //get login info
            $sql = "select l.user_id, count(l.id) as login from logs as l 
                    where l.message like '%LOGGED IN%' and l.user_id in (".$user_id.")
                    group by l.user_id";
            $p = $this->query($sql);
            $pval=set::extract($p,'{n}.l.user_id');
            
            //get comment info
            $sql1 = "select c.user_id,count(c.id) as comment from comments as c 
                    where c.user_id in (".$user_id.") group by c.user_id";
            $o = $this->query($sql1);
            $oval=set::extract($o,'{n}.c.user_id');
            
            //get no of updating of their own userstatus 
            $sql2 = "select us.updater,count(us.id) as feedback from userstatuses as us
                    where us.updater in (".$user_id.") and us.user_id=us.updater group by us.updater";
            $n=$this->query($sql2);
            $nval=set::extract($n,'{n}.us.updater');
            
            //get no of updating of their own groupstatus 
            $na=array();
            foreach($m as $mdata){
                $group=implode(',',set::extract($this->query("select ug.group_id from users_groups as ug where ug.user_id =".$mdata['u']['id']),'{n}.ug.group_id'));
                if($group){
                    $sql2a = $this->query("select gs.user_id,count(gs.id) as feedback from groupstatuses as gs 
                                        where gs.user_id =".$mdata['u']['id']." and gs.group_id in (".$group.") group by gs.user_id");       
                    if($sql2a){
                        $na[]=$sql2a;
                    }
                }
            }
            $naval=set::extract($na,'{n}.gs.user_id');
            
            foreach ($m as $mkey=>$mval) {
                $m[$mkey][0]['feedback'] =
                    $this->getInfo($mval['u']['id'],$nval,$n,'feedback')+
                    $this->getInfo($mval['u']['id'],$naval,$na,'feedback');
                $m[$mkey]['0']['comment']=$this->getInfo($mval['u']['id'],$oval,$o,'comment');
                $m[$mkey]['0']['login']=$this->getInfo($mval['u']['id'],$pval,$p,'login');
            }
            return $m;
            
        } else if ($type == 'MEETINGS') {
            // get num of meetings for each committee
            $sql = "select c.name, m.meeting_title, count(m.id) as kuantiti
                    from committees as c
                    join meetings as m on (c.id=m.committee_id and m.deleted!='1')
                    where c.deleted!='1'
                    group by m.meeting_title
                    order by c.name asc, m.meeting_title asc";
            $r = $this->query($sql);
            $meeting_title='"'.implode('","',Set::extract($r,'{n}.m.meeting_title')).'"';

            // get num of decisions for each meeting
            $sql4 = "select m.meeting_title, count(d.id) as numofdec
                    from meetings as m, decisions as d
                    where m.deleted!='1' and d.deleted!='1' and d.meeting_id=m.id 
                    and m.meeting_title in (".$meeting_title.") 
                    group by m.meeting_title";
            $q = $this->query($sql4);
            $qval=set::extract($q,'{n}.m.meeting_title'); 
            
            // combine all these into 1 nice array
            foreach ($r as $rkey=>$rval) {
                $r[$rkey]['0']['numofdec']=$this->getInfo($rval['m']['meeting_title'],$qval,$q,'numofdec');
            }
            return $r;
            
        }
       
    }
    
    /*describe getInfo
     * Find the needed information
     * @param $needle 
     * @param $haystack 
     * @param $array -the array of the info 
     * @param $info - variable name of the needed info
     * @return the needed value 
    */
    function getInfo($needle,$haystack,$array,$info){
        if(in_array($needle,$haystack)){
            $key=array_search($needle,$haystack);
            return $array[$key][0][$info];
        }else{
            return 0;
        }
    }

}
?>
